import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import path from 'path';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// SQLite数据库配置
const DB_PATH = path.join(__dirname, '..', '..', 'data', 'bike_wholesale.db');

class SQLiteDatabase {
  constructor() {
    this.db = null;
    this.isConnected = false;
  }

  // 连接数据库
  async connect() {
    return new Promise((resolve, reject) => {
      // 确保data目录存在
      const fs = require('fs');
      const dataDir = path.dirname(DB_PATH);
      if (!fs.existsSync(dataDir)) {
        fs.mkdirSync(dataDir, { recursive: true });
      }

      this.db = new sqlite3.Database(DB_PATH, (err) => {
        if (err) {
          console.error('❌ SQLite连接失败:', err.message);
          reject(err);
        } else {
          console.log('✅ SQLite数据库连接成功');
          this.isConnected = true;
          resolve(this);
        }
      });
    });
  }

  // 初始化数据库表
  async initializeTables() {
    const tables = [
      `CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        role TEXT DEFAULT 'user',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      
      `CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        price DECIMAL(10,2) NOT NULL,
        category TEXT,
        stock INTEGER DEFAULT 0,
        image_url TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )`,
      

    ];

    for (const tableSql of tables) {
      await this.run(tableSql);
    }
    
    console.log('✅ 数据库表结构初始化完成');
  }

  // 执行SQL查询
  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function(err) {
        if (err) {
          reject(err);
        } else {
          resolve({ id: this.lastID, changes: this.changes });
        }
      });
    });
  }

  // 查询数据
  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, row) => {
        if (err) {
          reject(err);
        } else {
          resolve(row);
        }
      });
    });
  }

  // 查询多条数据
  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  }

  // 关闭数据库连接
  close() {
    if (this.db) {
      this.db.close();
      this.isConnected = false;
      console.log('✅ SQLite数据库连接已关闭');
    }
  }

  // 测试连接
  async testConnection() {
    try {
      const result = await this.get('SELECT 1 as test');
      return result.test === 1;
    } catch (error) {
      console.error('SQLite连接测试失败:', error.message);
      return false;
    }
  }
}

// 创建全局数据库实例
const sqliteDB = new SQLiteDatabase();

export default sqliteDB;